/*
 * Copyright (C) 2007 James Deng (jtdeng@gmail.com)
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */


set echo off verify off showmode off feedback off
whenever sqlerror exit sql.sqlcode


conn / as sysdba
set serveroutput on

/*
 * Check library UTL_CONNECTION_LIB
 */
 
declare
	l_lib_name varchar2(30);
begin
	select LIBRARY_NAME into l_lib_name
	from dba_libraries 
	where LIBRARY_NAME='UTL_CONNECTION_LIB';
exception
	when NO_DATA_FOUND then
		raise_application_error(-20001, 'Library UTL_CONNECTION_LIB not found');
end;
/

/*
 * Create user PLMSN if it does not exist
 */

declare
	l_username varchar2(256);
begin	
	select username into l_username from all_users where username='PLMSN';
	raise_application_error(-20001, 'User PLMSN already exists');
exception
	when NO_DATA_FOUND then
		dbms_output.put_line('User PLMSN does not exist, create it now...');
		execute immediate 'create user plmsn identified by plmsn default tablespace users temporary tablespace temp';
		execute immediate 'grant connect,resource to plmsn';
		execute immediate 'grant create view to plmsn';
		execute immediate 'grant execute on dbms_pipe to plmsn'; 
		execute immediate 'grant create job to plmsn';
		execute immediate 'grant manage scheduler to plmsn';
		execute immediate 'grant execute on UTL_CONNECTION_LIB to plmsn';
		dbms_output.put_line('User PLMSN created.');
end;
/

conn plmsn/plmsn
set serveroutput on

/* 
 * Create types
 */
create or replace type attendees_t as varray(256) of varchar2(64);
/
show err;

/* 
 * Create Sequences
 */
CREATE SEQUENCE SEQ_TRANSACTION_ID
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 999999999999999999999999999
NOCACHE
CYCLE 
ORDER; 

CREATE SEQUENCE SEQ_CONVERSATION_ID
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 999999999999999999999999999
NOCACHE
CYCLE 
ORDER; 

CREATE SEQUENCE SEQ_OUTGOING_MSG_ID
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 999999999999999999999999999
NOCACHE
CYCLE 
ORDER;


/* 
 * Create tables
 */

create table owners ( 	owner_id number primary key,
						passport varchar2(64),
						display_name varchar2(1024),
						display_msg varchar2(1024),
						password varchar2(256),
						password_obfuscated varchar2(1) default 'N',
						status varchar2(3) check( status in ('FLN', 'NLN', 'BSY', 'IDL', 'BRB', 'AWY', 'PHN', 'LUN') ) );

create table contacts (	owner_id number,
						passport varchar2(64), 
						display_name varchar2(1024), 
						display_msg varchar2(1024), 
						status varchar2(3) check( status in ('FLN', 'NLN', 'BSY', 'IDL', 'BRB', 'AWY', 'PHN', 'LUN') ),						
						group_id number,
						FL varchar2(1),
						AL varchar2(1),
						BL varchar2(1),
						RL varchar2(1),
						PL varchar2(1), primary key (owner_id, passport) );

--save contacts that have ever removed me from their friend list or have ever been removed by me
--create table contacts_history

create table groups (	owner_id number,
						group_id number, 
						group_name varchar2(256), primary key (owner_id, group_id) );

create table conversations (conversation_id number primary key,
							owner_id number,
							inviter varchar2(64),
							session_id number,
							authentication varchar2(64),
							switchboard	varchar2(32),
							status	varchar2(8) check( status in ('ISSUED', 'BUSY', 'IDLE', 'CLOSED') ),
							attendees attendees_t,
							initial_attendee varchar2(64) )
							STORAGE (
								BUFFER_POOL	KEEP
							);

create table messages (	owner_id number,
						conversation_id number,
						time timestamp default systimestamp not null,
						direction varchar2(4) check( direction in ('FROM', 'TO') ),
						passport varchar2(64),
						msg_text varchar2(4000) );

create table outgoing_message_queue (	msg_id number primary key,
										owner_id number,
										conversation_id number,
										transaction_id number,
										receiver varchar2(64),
										msg_text varchar2(4000),
										msg_option varchar2(64) default 'Default' )
									STORAGE (
										BUFFER_POOL	KEEP
									);

create table message_option (	option_name varchar2(64) primary key,
								FN varchar2(32),
								EF varchar2(32),
								CO varchar2(32),
								CS varchar2(32), 
								PF varchar2(32) );
									
create table services (	service_id number primary key,
						service_name varchar2(32),
						service_cmd varchar2(32),
						entry_procedure varchar2(256),
						repeatable number,
						description varchar2(256),
						help_info varchar(1024) );
					
create table service_instances (	service_id number,
									owner_id number,
									passport varchar(64),
									job_name varchar(30), 
									created date default sysdate )
									STORAGE (
										BUFFER_POOL	KEEP
									);
									
create table jokes(	joke_id number primary key,
					who varchar2(64),
					created date default sysdate,					
					joke_text varchar2(4000) );
					
--create table public_messages(						
/*
 * Create views
 */


/*
 * Create packages
 */

@@utl_string.sql

@@utl_config.sql

@@utl_logging.sql

@@utl_connection.sql

@@utl_mime.sql

@@plmsn_session_spec.sql

@@plmsn_conversation_spec.sql

@@plmsn_events_spec.sql

@@plmsn_services_spec.sql

@@plmsn_session_body.sql

@@plmsn_conversation_body.sql

@@plmsn_events_body.sql

@@plmsn_services_body.sql

@@plmsn_admin.sql

/*
 * Setup initial parameters
 */
prompt 'Setting up initial parameters...'

declare

begin	
	utl_config.set_cfg('http_proxy', NULL);	
	utl_config.set_cfg('initial_status', 'NLN');
	utl_config.set_cfg('wallet_path', 'file:/usr/lib/oracle/xe/WALLETS');
	utl_config.set_cfg('wallet_password', 'oracle123');
	utl_config.set_cfg('about_information', 'Powered by PL/MSN and Oracle XE on Debian, visit http://jtdeng.3322.org:8080');
	
	--Predefined Message Options
	insert into message_option (option_name, FN, EF, CO, CS, PF) 		
	values ('Default', 'Fixedsys', null, '0', '86', '31');

	insert into message_option (option_name, FN, EF, CO, CS, PF) 		
	values ('Fixedsys_Green_Normal', 'Fixedsys', null, '8000', '86', '31');
	
	insert into message_option (option_name, FN, EF, CO, CS, PF) 		
	values ('Fixedsys_Red_Normal', 'Fixedsys', null, 'ff', '86', '31');
	
	--Predefined MSN Owners
	insert into owners ( owner_id, passport, display_name, display_msg, password, password_obfuscated, status )
	values ( 0, 'yourname@hotmail.com', 'PLMSN MSN Robot', 'Happy Bot', '******', 'N', 'NLN' );
	
	--Predefined Services
	insert into services ( service_id, service_name, service_cmd, entry_procedure, repeatable, description, help_info)
	values (0, 'Echo', '/echo', 'plmsn_services.svc_echo', 1, 'echo your messages', '/echo[:<interval>][:<message>]');
		
	insert into services ( service_id, service_name, service_cmd, entry_procedure, repeatable, description, help_info)
	values (1, 'Stock', '/stock', 'plmsn_services.svc_stock', 1, 'Chinese stock market', '/stock[:<interval>]:<comma_separated_stock_code_list>');
	
--	insert into services ( service_id, service_name, service_cmd, entry_procedure, repeatable, description, help_info)
--	values (3, 'Joke', '/joke', 'plmsn_services.svc_joke', 0, 'Upload a joke', '/joke:<message>');
	commit;
end;
/

prompt 'Done.'